package com.sams.service.impl;

import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.sams.entity.Baseinfo;
import com.sams.entity.CountrySub;
import com.sams.entity.DataTotal;
import com.sams.entity.DataUp;
import com.sams.entity.GovSub;
import com.sams.entity.Processmanage;
import com.sams.entity.SchoolSub;
import com.sams.entity.SocietySub;
import com.sams.entity.SpotUpload;
import com.sams.mapper.DataTotalMapper;
import com.sams.service.BaseInfoService;
import com.sams.service.DataTotalService;

@Service
public class DataTotalServiceImpl implements DataTotalService{
	
	@Autowired
	private DataTotalMapper mapper;
	@Autowired
	private BaseInfoService baseInfoService;
	/**
	 * 国家资助归档查询
	 */
	public PageInfo<CountrySub> selectCountrySub(Integer currentPage ,
			DataTotal dataTotal) {
		// 1 定义当前页  和 一页显示的行数 
		  PageHelper.startPage(currentPage, 20);  
		 
		  List<CountrySub> list = mapper.selectCountrySub(dataTotal);
		  PageInfo<CountrySub> pageInfo = new PageInfo<CountrySub>(list);
			return pageInfo;
	}
	
	/**
	 * 查询所有的镇列表
	 */
	public List<Baseinfo> selectAllZhen() {
		// TODO Auto-generated method stub
		return mapper.selectAllZhen();
	}
	/**
	 * 根据镇id 查询所有的学校
	 */
	public List<Baseinfo> selectByZid(Integer zid) {
		// TODO Auto-generated method stub
		return baseInfoService.listAll(zid);
	}
	/**
	 * 导出excel数据 
	 * @param titles
	 * @param out
	 * @param dataTotal
	 * @throws Exception
	 */
	public void export(String[] titles, ServletOutputStream out,DataTotal dataTotal) throws Exception{
		   try{
               // 第一步，创建一个workbook，对应一个Excel文件
               HSSFWorkbook workbook = new HSSFWorkbook();
               
               // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
               HSSFSheet hssfSheet = workbook.createSheet("归档数据");
               
//               // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
//               
//               HSSFRow row = hssfSheet.createRow(0);
//              // 第四步，创建单元格，并设置值表头 设置表头居中
//               HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
//               
//               //居中样式
//               hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//   
//               HSSFFont font = workbook.createFont();
//               font.setFontName("黑体");
//              // font.setFontHeightInPoints((short) 16);
//               font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//               hssfCellStyle.setFont(font);//选择需要用到的字体格式
//               
//               HSSFCell hssfCell = null;
//               for (int i = 0; i < titles.length; i++) {
//                   hssfCell = row.createCell(i);//列索引从0开始
//                   hssfCell.setCellValue(titles[i]);//列名1
//                   hssfCell.setCellStyle(hssfCellStyle);//列居中显示      '    
//                   
//               }
         
               createExcelHeader(hssfSheet, workbook);
               
               HSSFRow row =null;
              //查询国家数据 
               List<CountrySub> list = mapper.selectCountrySub(dataTotal);
               System.out.println("list============="+list.size());
                   for (int i = 0; i < list.size(); i++) {
                       row = hssfSheet.createRow(i+2);                
                       CountrySub sub = list.get(i);
                       // 第六步，创建单元格，并设置值
                       /**
                        * 序号", "受助项目", "学校", "姓名" ,"性别","年级(一、二、三)","班级(1,2,3)",
                    		  "身份证号","学籍号","受助类别","详细原因","省","市","县",
                    		  "镇","村(社区、街道)","组(路)","家长地址","与学生关系","联系电话","银行卡或存折号",
                    		  "银行名称","资助金额或免学费金额(元"}; 
                        */
                      row.createCell(0).setCellValue(i+1);
                      row.createCell(1).setCellValue(sub.getProject());
                       row.createCell(2).setCellValue(sub.getSchoolname());
                       row.createCell(3).setCellValue(sub.getName());
                       row.createCell(4).setCellValue(sub.getSex());
                       row.createCell(5).setCellValue(sub.getGrade());
                       row.createCell(6).setCellValue(sub.getClasses());
                       row.createCell(7).setCellValue(sub.getIdcard());
                       row.createCell(8).setCellValue(sub.getStucode());
                       row.createCell(9).setCellValue(sub.getType());
                       row.createCell(10).setCellValue(sub.getDescinfo());
                       row.createCell(11).setCellValue(sub.getProvince());
                       row.createCell(12).setCellValue(sub.getCity());
                       row.createCell(13).setCellValue(sub.getXian());
                       row.createCell(14).setCellValue(sub.getTown());
                       row.createCell(15).setCellValue(sub.getCountry());
                       row.createCell(16).setCellValue(sub.getCountrygroup());
                       row.createCell(17).setCellValue(sub.getProvince()+"省"
                    		   +sub.getCity()+"市"+sub.getTown()+"镇"+sub.getCountry()+"村");//家庭地址
                       row.createCell(18).setCellValue(sub.getParentname());
                       row.createCell(19).setCellValue(sub.getRelation());
                       row.createCell(20).setCellValue(sub.getPhone());
                       row.createCell(21).setCellValue(sub.getBankno());
                       row.createCell(22).setCellValue(sub.getBankname());
                       row.createCell(23).setCellValue(sub.getAmount().toString());
                
                       
                   }

               // 第七步，将文件输出到客户端浏览器
               try {
                   workbook.write(out);
                   out.flush();
                  out.close();
   
               } catch (Exception e) {
                   e.printStackTrace();
               }
           }catch(Exception e){
               e.printStackTrace();
              throw new Exception("导出信息失败！");
              
              
           }        
		
	}
	/**
	 * 生成excel的表头
	 * @param hssfSheet
	 * @param workbook
	 */
	public void createExcelHeader(HSSFSheet hssfSheet,HSSFWorkbook workbook) {
		String[] titles_1 = { "序号", "受助项目(详见说明)", "学校(幼儿园)名称", "受助学生信息", "受助原因", "家庭住址(填写户口簿住址)", "家长(监护人)信息", "账户信息" };
		HSSFRow row = hssfSheet.createRow(0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
		// 居中样式
		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = workbook.createFont();
		font.setFontName("黑体");
		// font.setFontHeightInPoints((short) 16);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		hssfCellStyle.setFont(font);// 选择需要用到的字体格式

		HSSFCell hssfCell = null;
 	 
		for (int i = 0; i < 4; i++) {
			hssfCell = row.createCell(i);// 列索引从0开始
			hssfCell.setCellValue(titles_1[i]);// 列名1
			hssfCell.setCellStyle(hssfCellStyle);// 列居中显示 '
		}
		  CellRangeAddress region = new CellRangeAddress(0, 1, 0, 0);
		  hssfSheet.addMergedRegion(region);
		  CellRangeAddress region2 = new CellRangeAddress(0, 1, 1, 1);
		  hssfSheet.addMergedRegion(region2);
		  CellRangeAddress region3 = new CellRangeAddress(0, 1, 2, 2);
		  hssfSheet.addMergedRegion(region3);
		  CellRangeAddress region4 = new CellRangeAddress(0, 0, 3, 8);
		  hssfSheet.addMergedRegion(region4);
		
		    hssfCell = row.createCell(9);// 列索引从0开始
			hssfCell.setCellValue(titles_1[4]);// 列名1
			hssfCell.setCellStyle(hssfCellStyle);
		     CellRangeAddress region5 = new CellRangeAddress(0, 0, 9, 10);
			 hssfSheet.addMergedRegion(region5);
			 
			  hssfCell = row.createCell(11); 
			  hssfCell.setCellValue(titles_1[5]);// 列名1
			  hssfCell.setCellStyle(hssfCellStyle);
			  CellRangeAddress region6 = new CellRangeAddress(0, 0, 11, 16);
				 hssfSheet.addMergedRegion(region6);
				 
				  hssfCell = row.createCell(17); 
				  hssfCell.setCellValue(titles_1[6]);// 列名1
				  hssfCell.setCellStyle(hssfCellStyle);
				  CellRangeAddress region7 = new CellRangeAddress(0, 0, 17, 20);
					 hssfSheet.addMergedRegion(region7);
					 
					  hssfCell = row.createCell(21); 
					  hssfCell.setCellValue(titles_1[7]);// 列名1
					  hssfCell.setCellStyle(hssfCellStyle);
					  CellRangeAddress region8 = new CellRangeAddress(0, 0, 21, 23);
						 hssfSheet.addMergedRegion(region8);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

		HSSFRow row2 = hssfSheet.createRow(1);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle hssfCellStyle2 = workbook.createCellStyle();

		// 居中样式
		hssfCellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		HSSFFont font2 = workbook.createFont();
		font2.setFontName("黑体");
		// font.setFontHeightInPoints((short) 16);
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		hssfCellStyle2.setFont(font2);// 选择需要用到的字体格式

		String[] titles_2 = { "序号", "受助项目", "学校", "姓名", "性别", "年级", "班级", "身份证号", "学籍号", "受助类别", "详细原因", "省", "市", "县",
				"镇", "村(社区、街道)", "组(路)", "家长地址", "家长姓名", "与学生关系", "联系电话", "银行卡或存折号", "银行名称", "资助金额或免学费金额(元" };
	
		HSSFCell hssfCell2 = null;
		for (int i = 0; i < titles_2.length; i++) {
			hssfCell2 = row2.createCell(i);// 列索引从0开始
			hssfCell2.setCellValue(titles_2[i]);// 列名1
			hssfCell2.setCellStyle(hssfCellStyle2);// 列居中显示 '

		}

	}


	/**
	 * 政府归档数据查询
	 */
	public PageInfo<GovSub> selectGovSub(Integer currentPage, DataTotal dataTotal) {
		// 1 定义当前页  和 一页显示的行数 
		  PageHelper.startPage(currentPage, 20);  
		 
		  List<GovSub> list = mapper.selectGovSub(dataTotal);
		  PageInfo<GovSub> pageInfo = new PageInfo<GovSub>(list);
			return pageInfo;
	}
	/**
	 * 导出归档数据
	 */

	public void exportGovSub(String[] titles, ServletOutputStream out, DataTotal dataTotal) throws Exception {
		   try{
               // 第一步，创建一个workbook，对应一个Excel文件
               HSSFWorkbook workbook = new HSSFWorkbook();
               
               // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
               HSSFSheet hssfSheet = workbook.createSheet("政府资助归档数据");
               
               // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
               
               HSSFRow row = hssfSheet.createRow(0);
              // 第四步，创建单元格，并设置值表头 设置表头居中
               HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
               
               //居中样式
               hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   
               HSSFFont font = workbook.createFont();
               font.setFontName("黑体");
              // font.setFontHeightInPoints((short) 16);
               font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
               hssfCellStyle.setFont(font);//选择需要用到的字体格式
               
               HSSFCell hssfCell = null;
               for (int i = 0; i < titles.length; i++) {
                   hssfCell = row.createCell(i);//列索引从0开始
                   hssfCell.setCellValue(titles[i]);//列名1
                   hssfCell.setCellStyle(hssfCellStyle);//列居中显示      '    
                   
               }
         
              //查询数据 
               List<GovSub> list = mapper.selectGovSub(dataTotal);
               System.out.println("list============="+list.size());
                   for (int i = 0; i < list.size(); i++) {
                       row = hssfSheet.createRow(i+1);                
                       GovSub sub = list.get(i);
                       // 第六步，创建单元格，并设置值
                      row.createCell(0).setCellValue(i+1);
                      row.createCell(1).setCellValue(sub.getName());
                       row.createCell(2).setCellValue(sub.getStucode());
                       row.createCell(3).setCellValue(sub.getIdcardtype());
                       row.createCell(4).setCellValue(sub.getIdcard());
                       row.createCell(5).setCellValue(sub.getSex());
                       row.createCell(6).setCellValue(sub.getBirthday());
                       row.createCell(7).setCellValue(sub.getYear());
                       row.createCell(8).setCellValue(sub.getStudyyear());
                       row.createCell(9).setCellValue(sub.getTerm());
                       row.createCell(10).setCellValue(sub.getSubname());
                       row.createCell(11).setCellValue(sub.getSubamount());
                       row.createCell(12).setCellValue(sub.getProvidedate());
                       row.createCell(13).setCellValue(sub.getProvide());
                   }

               // 第七步，将文件输出到客户端浏览器
               try {
                   workbook.write(out);
                   out.flush();
                  out.close();
   
               } catch (Exception e) {
                   e.printStackTrace();
               }
           }catch(Exception e){
               e.printStackTrace();
              throw new Exception("导出信息失败！");
              
              
           }   
		
	}

	public PageInfo<SchoolSub> selectSchoolSub(Integer currentPage, DataTotal dataTotal) {
		// 1 定义当前页  和 一页显示的行数 
		  PageHelper.startPage(currentPage, 20);  
		 
		  List<SchoolSub> list = mapper.selectSchoolSub(dataTotal);
		  PageInfo<SchoolSub> pageInfo = new PageInfo<SchoolSub>(list);
			return pageInfo;
	}

	public void exportSchoolSub(String[] titles, ServletOutputStream out, DataTotal dataTotal) throws Exception {
		   try{
               // 第一步，创建一个workbook，对应一个Excel文件
               HSSFWorkbook workbook = new HSSFWorkbook();
               
               // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
               HSSFSheet hssfSheet = workbook.createSheet("学校资助归档数据");
               
               // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
               
               HSSFRow row = hssfSheet.createRow(0);
              // 第四步，创建单元格，并设置值表头 设置表头居中
               HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
               
               //居中样式
               hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   
               HSSFFont font = workbook.createFont();
               font.setFontName("黑体");
              // font.setFontHeightInPoints((short) 16);
               font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
               hssfCellStyle.setFont(font);//选择需要用到的字体格式
               
               HSSFCell hssfCell = null;
               for (int i = 0; i < titles.length; i++) {
                   hssfCell = row.createCell(i);//列索引从0开始
                   hssfCell.setCellValue(titles[i]);//列名1
                   hssfCell.setCellStyle(hssfCellStyle);//列居中显示      '    
                   
               }
         
             
               
              // HSSFRow row =null;
              //查询数据 
               List<SchoolSub> list = mapper.selectSchoolSub(dataTotal);
                   for (int i = 0; i < list.size(); i++) {
                       row = hssfSheet.createRow(i+1);                
                       SchoolSub sub = list.get(i);
                       // 第六步，创建单元格，并设置值
                      row.createCell(0).setCellValue(i+1);
                      row.createCell(1).setCellValue(sub.getName());
                       row.createCell(2).setCellValue(sub.getStucode());
                       row.createCell(3).setCellValue(sub.getIdcardtype());
                       row.createCell(4).setCellValue(sub.getIdcard());
                       row.createCell(5).setCellValue(sub.getSex());
                       row.createCell(6).setCellValue(sub.getBirthday());
                       row.createCell(7).setCellValue(sub.getYear());
                       row.createCell(8).setCellValue(sub.getStudayyear());
                       row.createCell(9).setCellValue(sub.getTerm());
                       row.createCell(10).setCellValue(sub.getSubname());
                       row.createCell(11).setCellValue(sub.getSubamount());
                       row.createCell(12).setCellValue(sub.getProvidedate());
                       row.createCell(13).setCellValue(sub.getProvide());
                   }

               // 第七步，将文件输出到客户端浏览器
               try {
                   workbook.write(out);
                   out.flush();
                  out.close();
   
               } catch (Exception e) {
                   e.printStackTrace();
               }
           }catch(Exception e){
               e.printStackTrace();
              throw new Exception("导出信息失败！");
              
              
           }   
		
	}

	public PageInfo<SocietySub> selectSocietySub(Integer currentPage, DataTotal dataTotal) {
		// 1 定义当前页  和 一页显示的行数 
		  PageHelper.startPage(currentPage, 20);  
		 
		  List<SocietySub> list = mapper.selectSocietySub(dataTotal);
		  PageInfo<SocietySub> pageInfo = new PageInfo<SocietySub>(list);
			return pageInfo;
	}

	public void exportSocietySub(String[] titles, ServletOutputStream out, DataTotal dataTotal) throws Exception {
		   try{
               // 第一步，创建一个workbook，对应一个Excel文件
               HSSFWorkbook workbook = new HSSFWorkbook();
               
               // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
               HSSFSheet hssfSheet = workbook.createSheet("社会资助归档数据");
               
               // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
               
               HSSFRow row = hssfSheet.createRow(0);
              // 第四步，创建单元格，并设置值表头 设置表头居中
               HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
               
               //居中样式
               hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   
               HSSFFont font = workbook.createFont();
               font.setFontName("黑体");
              // font.setFontHeightInPoints((short) 16);
               font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
               hssfCellStyle.setFont(font);//选择需要用到的字体格式
               
               HSSFCell hssfCell = null;
               for (int i = 0; i < titles.length; i++) {
                   hssfCell = row.createCell(i);//列索引从0开始
                   hssfCell.setCellValue(titles[i]);//列名1
                   hssfCell.setCellStyle(hssfCellStyle);//列居中显示      '    
                   
               }
         
             
               
              // HSSFRow row =null;
              //查询数据 
               List<SocietySub> list = mapper.selectSocietySub(dataTotal);
                   for (int i = 0; i < list.size(); i++) {
                       row = hssfSheet.createRow(i+1);                
                       SocietySub sub = list.get(i);
                       // 第六步，创建单元格，并设置值
                      row.createCell(0).setCellValue(i+1);
                      row.createCell(1).setCellValue(sub.getName());
                       row.createCell(2).setCellValue(sub.getStucode());
                       row.createCell(3).setCellValue(sub.getIdcardtype());
                       row.createCell(4).setCellValue(sub.getIdcard());
                       row.createCell(5).setCellValue(sub.getSex());
                       row.createCell(6).setCellValue(sub.getBirthday());
                       row.createCell(7).setCellValue(sub.getYear());
                       row.createCell(8).setCellValue(sub.getStudayyear());
                       row.createCell(9).setCellValue(sub.getTerm());
                       row.createCell(10).setCellValue(sub.getSubname());
                       row.createCell(11).setCellValue(sub.getSubamount());
                       row.createCell(12).setCellValue(sub.getProvidedate());
                       row.createCell(13).setCellValue(sub.getProvide());
                   }

               // 第七步，将文件输出到客户端浏览器
               try {
                   workbook.write(out);
                   out.flush();
                  out.close();
   
               } catch (Exception e) {
                   e.printStackTrace();
               }
           }catch(Exception e){
               e.printStackTrace();
              throw new Exception("导出信息失败！");
              
              
           }   
		
		
	}
	/**
	 * 归档查询 文件图片上传
	 */
	public PageInfo<Processmanage> selectProcess(Integer currentPage, DataTotal dataTotal) {
		// 1 定义当前页  和 一页显示的行数 
		  PageHelper.startPage(currentPage, 20);  
		 
		  List<Processmanage> list = mapper.selectProcess(dataTotal);
		  PageInfo<Processmanage> pageInfo = new PageInfo<Processmanage>(list);
			return pageInfo;
	 
	}
	//归档统计 现场图片上传
	public PageInfo<SpotUpload> selectSpotUpload(Integer currentPage, DataTotal dataTotal) {
		// 1 定义当前页  和 一页显示的行数 
		  PageHelper.startPage(currentPage, 20);  
		 
		  List<SpotUpload> list = mapper.selectSpotUpload(dataTotal);
		  PageInfo<SpotUpload> pageInfo = new PageInfo<SpotUpload>(list);
			return pageInfo;
	}
}
